package com.sgcc.manager.sql.biz.impl;

import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.support.rowset.SqlRowSet;
import org.springframework.jdbc.support.rowset.SqlRowSetMetaData;
import org.springframework.stereotype.Service;

import com.sgcc.manager.sql.biz.ISqlBiz;
import com.sgcc.manager.sql.dao.ISqlDao;
@Service("sqlBizImpl")
public class SqlBizImpl implements ISqlBiz{
	Logger logger=Logger.getLogger(this.getClass().getName());
	@Autowired
	private ISqlDao SqlDaoImpl;
	/**
	 * 
	 * DLL操作： 创建表、字段或者提交、回滚数据的操作
	 * @param sessionId
	 * @param sql
	 * @throws Exception
	 */
	public void execute(String sessionId,String sql)throws Exception {
		SqlDaoImpl.execute(sessionId,sql);
	}


	public List queryList(String sessionId,String sql) throws Exception{
		return SqlDaoImpl.queryList(sessionId,sql);
	}

	public int update(String sessionId,String sql) throws Exception{
		return SqlDaoImpl.update(sessionId,sql);
	}
	
	/**
	 * 根据页号查询单页记录
	 * @param sessionId
	 * @param sql
	 * @param currentPage 页号
	 * @return
	 * @throws Exception
	 */
	public Map queryForMap(String sessionId,String sql, int currentPage)throws Exception {
		//判断数据总数
		int totalCount=this.SqlDaoImpl.queryForCount(sessionId,sql);
		int pageSize=20;
		//分页处理
		int totalPages = totalCount/pageSize;
		if(totalCount%pageSize > 0){
			totalPages = totalPages + 1;
		}
		if(totalCount<=20){
			currentPage=-1;
		}
		//取数据
		SqlRowSet sqlRowSet = this.SqlDaoImpl.queryForRowSet(sessionId,sql,currentPage,pageSize);
		//处理列字段信息
		SqlRowSetMetaData sqlRsmd = sqlRowSet.getMetaData();
		int columnCount = sqlRsmd.getColumnCount();
		//字段
		List<Map<String,String>> tableFieldList=new ArrayList<Map<String,String>>();
		//数据
		List<Map<String, Object>> valueList=new ArrayList<Map<String,Object>>();
		//处理表头
		for (int i = 1; i <= columnCount; i++) {
			Map<String,String> fieldMap = new HashMap<String,String>();
			fieldMap.put("name", sqlRsmd.getColumnName(i));
			fieldMap.put("fieldType", String.valueOf(sqlRsmd.getColumnType(i)));
			tableFieldList.add(fieldMap);
		}
		//处理结果集
		 while(sqlRowSet.next()){  
			 Map<String,Object> valueMap = new HashMap<String,Object>();
			 for (int i = 1; i <= columnCount; i++) {
				 valueMap.put(sqlRsmd.getColumnName(i), sqlRowSet.getString(sqlRsmd.getColumnName(i)));
			 }
			 valueList.add(valueMap);
		} 
		 System.out.println("查询记录数query----"+valueList.size());
		Map returnMap=new HashMap();
		returnMap.put("fieldList", tableFieldList);
		returnMap.put("valueList", valueList);
		returnMap.put("currentPage", currentPage);
		returnMap.put("pageSql", sql);
		returnMap.put("totalPages", totalPages);
		returnMap.put("totalCount", totalCount);
		return returnMap;
	}


	/**
	 * <br>Description:根据type类型查询记录
	 * @param sessionId
	 * @param sql
	 * @param type 查询类型 
	 * @param currentPage 当前页
	 * @param totalPages 总页数
	 * @return
	 * @throws Exception
	 */
	public Map queryPageForMap(String sessionId,String sql, String type, int currentPage, int totalPages)throws Exception {
		Map returnMap=new HashMap();
		int pageSize=20;
		if(StringUtils.isNotEmpty(type) && (type.equalsIgnoreCase("next") ||  type.equalsIgnoreCase("all")||type.equalsIgnoreCase("excel"))){
			SqlRowSet sqlRowSet=null;
			if(type.equalsIgnoreCase("excel")){
				int begin=1;
				int end =(currentPage-1)*pageSize+1+ pageSize;
				//取数据
				sqlRowSet = this.SqlDaoImpl.queryRowSet(sessionId,sql,begin,end);
			}else{
				//取数据
				sqlRowSet = this.SqlDaoImpl.queryForRowSet(sessionId,sql,currentPage,pageSize);
			}
			//处理列字段信息
			SqlRowSetMetaData sqlRsmd = sqlRowSet.getMetaData();
			int columnCount = sqlRsmd.getColumnCount();
			//字段
			List<Map<String,String>> tableFieldList=new ArrayList<Map<String,String>>();
			//数据
			List<Map<String, Object>> valueList=new ArrayList<Map<String,Object>>();
			//处理表头
			for (int i = 1; i <= columnCount; i++) {
				Map<String,String> fieldMap = new HashMap<String,String>();
				fieldMap.put("name", sqlRsmd.getColumnName(i));
				fieldMap.put("fieldType", String.valueOf(sqlRsmd.getColumnType(i)));
				tableFieldList.add(fieldMap);
			}
			//处理结果集
			 while(sqlRowSet.next()){  
				 Map<String,Object> valueMap = new HashMap<String,Object>();
				 for (int i = 1; i <= columnCount; i++) {
					 valueMap.put(sqlRsmd.getColumnName(i), sqlRowSet.getString(sqlRsmd.getColumnName(i)));
				 }
				 valueList.add(valueMap);
			} 
			 System.out.println("查询记录数----"+valueList.size());
			returnMap.put("fieldList", tableFieldList);
			returnMap.put("valueList", valueList);
			returnMap.put("currentPage", currentPage);
			returnMap.put("pageSql", sql);
			returnMap.put("totalPages", totalPages);
		}else {
			
		}
		return returnMap;
	}

	/**
	 * 
	 * IP方式连接数据库
	 * @param sessionId
	 * @param ipAdd
	 * @param db 数据库SID号
	 * @param username 用户名
	 * @param password 密码
	 * @param type
	 * @return
	 */
	public boolean LinkDB(String sessionId,String ipAdd, String db, String username,String password,String type) {
		return this.SqlDaoImpl.LinkDB(sessionId,ipAdd, db, username, password, type);
	}

	/**
	 * 
	 * oracle集群方式连接数据库
	 * @param sessionId
	 * @param oracleUrl 集群url
	 * @param username 用户名
	 * @param password 密码
	 * @param type
	 * @return
	 */
	public boolean LinkDB(String sessionId,String oracleUrl, String username, String password,String type) {
		return this.SqlDaoImpl.LinkDB(sessionId,oracleUrl, username, password, type);
		
	}
	
	/**
	 * jxl方式导出excel文件
	 * @param dataMap 数据
	 * @param os 输出流
	 * @param exportName 标签页名称
	 */
	
	public void exportExcel(Map dataMap, OutputStream os, String exportName) {
		/*		//导出excel
		List<Map<String,String>> tableFieldList=new ArrayList<Map<String,String>>();
		//数据
		List<Map<String, Object>> valueList=new ArrayList<Map<String,Object>>();
		//获取字段列表
		tableFieldList=(List<Map<String, String>>) dataMap.get("fieldList");
		//获取数据列表
		valueList=(List<Map<String, Object>>) dataMap.get("valueList");
		//
		WritableWorkbook wwb = null;
		try {
			// 首先要使用Workbook类的工厂方法创建一个可写入的工作薄(Workbook)对象
			wwb = Workbook.createWorkbook(os);
		} catch (IOException e) {
			logger.error("--------导出excel时创建工作薄错误！--------");
			logger.error(e.toString());
			e.printStackTrace();		
		}
		if (wwb != null) {
			WritableSheet sheet1 = wwb.createSheet(exportName, 0);
			WritableFont titleFont = new WritableFont(WritableFont.createFont("宋体"), 11, WritableFont.BOLD);
			WritableCellFormat titleFormat = new WritableCellFormat(titleFont);
			WritableFont contentFont = new WritableFont(WritableFont.createFont("宋体"), 9, WritableFont.NO_BOLD);
			WritableCellFormat contentFormat = new WritableCellFormat(	contentFont);
			try {
				titleFormat.setAlignment(jxl.format.Alignment.CENTRE);
				// 把垂直对齐方式指定为居中
				titleFormat.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
				// 设置自动换行
				titleFormat.setWrap(true);

				contentFormat.setAlignment(jxl.format.Alignment.CENTRE);
				// 把垂直对齐方式指定为居中
				contentFormat.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
				// 设置自动换行
				contentFormat.setWrap(true);
				//处理标题
				if(null!=tableFieldList&&tableFieldList.size()>0){
					for(int i=0;i<tableFieldList.size();i++){
						Map<String, String> map=tableFieldList.get(i);
						String nameStr=map.get("name");
						byte[] bstrLength = nameStr.getBytes(); //中文字符算两个字节
						sheet1.setColumnView(i, bstrLength.length+8);
						sheet1.addCell(new Label(i, 0,nameStr, titleFormat));
					}
				}
				//处理数据
				if(null!=valueList&&valueList.size()>0){
					for(int i=0;i<valueList.size();i++){
						//值列表
						Map<String, Object> map=valueList.get(i);
						if(null!=tableFieldList&&tableFieldList.size()>0){
							for(int j=0;j<tableFieldList.size();j++){
								Map<String, String> fieldMap=tableFieldList.get(j);
								String name=fieldMap.get("name");
								Object data=map.get(name);
								sheet1.addCell(new Label(j, i+1,null==data?"":data.toString(),contentFormat));
							}
						}
					}
				}
				
			} catch (Exception e) {
				logger.error("--------导出excel时工作薄添加数据错误！--------");
				logger.error(e.toString());
				e.printStackTrace();
			}finally{
				try {
					wwb.write();// 从内存中写入文件中
					wwb.close();// 关闭资源，释放内存
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		}
		 */
	}	
	
}
